<!DOCTYPE html><html><head><title>MySQL学习笔记（Day005：slow_log/generic_log/audit/存储引擎一）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-4845" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day005slowloggenericlogaudit存储引擎一">MySQL学习笔记（Day005：slow_log/generic_log/audit/存储引擎一）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day005slowloggenericlogaudit存储引擎一">MySQL学习笔记（Day005：slow_log/generic_log/audit/存储引擎一）</a><ul>
<li><a href="#一-慢查询日志进阶">一. 慢查询日志进阶</a><ul>
<li><a href="#1-相关参数">1. 相关参数：</a></li>
<li><a href="#2-慢查询日志实践">2. 慢查询日志实践</a></li>
</ul>
</li>
<li><a href="#二-通用日志genericlog与审计">二. 通用日志(generic_log)与审计</a><ul>
<li><a href="#1-通用日志作用">1. 通用日志作用</a></li>
<li><a href="#2-审计插件">2. 审计插件</a></li>
<li><a href="#3-audit-plugin安装">3.  Audit Plugin安装</a></li>
</ul>
</li>
<li><a href="#三-存储引擎一">三. 存储引擎(一)</a><ul>
<li><a href="#1mysql上支持的存储引擎">1.Mysql上支持的存储引擎</a></li>
<li><a href="#2-存储引擎的概念">2. 存储引擎的概念</a></li>
<li><a href="#3-mysql存储引擎">3. MySQL存储引擎</a></li>
<li><a href="#3-存储引擎之myisam">3. 存储引擎之MyISAM</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div></div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-慢查询日志进阶">一. 慢查询日志进阶</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-相关参数">1. 相关参数：</h3>

<ul><li><p><strong><code>slow_query_log</code></strong></p>

<ul>
<li>是否开启慢查询日志                </li></ul></li>
<li><p><strong><code>slow_query_log_file</code></strong></p>

<ul>
<li>慢查询日志文件名, 在<code>my.cnf</code>我们已经定义为slow.log，默认是 <em>机器名-slow.log</em>          </li></ul></li>
<li><p><strong><code>long_query_time</code></strong></p>

<ul>
<li>制定慢查询阈值, 单位是秒，且当版本 <code>&gt;=5.5.X</code>，支持毫秒。例如<code>0.5</code>即为<code>500ms</code></li>
<li><code>大于</code>该值，不包括值本身。例如该值为2，则执行时间正好<code>等于</code>2的SQL语句<code>不会记录</code></li></ul></li>
<li><p><strong><code>log_queries_not_using_indexes</code></strong></p>

<ul>
<li>将没有使用索引的SQL记录到慢查询日志  <br>
<ul>
<li>如果一开始因为数据少，查表快，耗时的SQL语句没被记录，当数据量大时，该SQL可能会执行很长时间</li>
<li>需要测试阶段就要发现问题，减小上线后出现问题的概率</li></ul></li></ul></li>
<li><p><strong><code>log_throttle_queries_not_using_indexes</code></strong></p>

<ul>
<li>限制每分钟内，在慢查询日志中，去记录没有使用索引的SQL语句的次数；版本需要<code>&gt;=5.6.X</code> <br>
<ul>
<li>因为没有使用索引的SQL可能会短时间重复执行，为了避免日志快速增大，限制每分钟的记录次数</li></ul></li></ul></li>
<li><p><strong><code>min_examined_row_limit</code></strong></p>

<ul>
<li>扫描记录少于改值的SQL不记录到慢查询日志  <br>
<ul>
<li>结合去记录没有使用索引的SQL语句的例子，有可能存在某一个表，数据量维持在百行左右，且没有建立索引。这种表即使不建立索引，查询也很快，扫描记录很小，如果确定有这种表，则可以通过此参数设置，将这个SQL不记录到慢查询日志。</li></ul></li></ul></li>
<li><p><strong><code>log_slow_admin_statements</code></strong></p>

<ul>
<li>记录超时的管理操作SQL到慢查询日志，比如ALTER/ANALYZE TABLE</li></ul></li>
<li><p><strong><code>log_output</code></strong></p>

<ul>
<li>慢查询日志的格式，[FILE | TABLE | NONE]，默认是FILE；版本<code>&gt;=5.5</code></li>
<li>如果设置为TABLE，则记录的到<code>mysql.slow_log</code></li></ul></li>
<li><p><strong><code>log_slow_slave_statements</code></strong></p>

<ul>
<li>在从服务器上开启慢查询日志</li></ul></li>
<li><p><strong><code>log_timestamps</code></strong></p>

<ul>
<li>写入时区信息。可根据需求记录UTC时间或者服务器本地系统时间</li></ul></li>
</ul>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="2-慢查询日志实践">2. 慢查询日志实践</h3>

<ul><li>设置慢查询记录的相关参数</li>
</ul>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 终端A</span><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 注意做实验以前，先把my.cnf中的 slow_query_log = 0, 同时将min_examined_row_limit = 100 进行注释</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">-----------+</span><br>| version() |<br>+<span class="hljs-comment">-----------+</span><br>| 5.7.9-log |<br>+<span class="hljs-comment">-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"slow_query_log"</span>； <span class="hljs-comment">-- 为了测试，特地在my.cnf中关闭了该选项</span><br>+<span class="hljs-comment">----------------+-------+</span><br>| Variable_name  | <span class="hljs-keyword">Value</span> |<br>+<span class="hljs-comment">----------------+-------+</span><br>| slow_query_log | OFF   |<br>+<span class="hljs-comment">----------------+-------+</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> slow_query_log = <span class="hljs-number">1</span>;</span>         <span class="hljs-comment">-- slow_query_log可以在线打开</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"slow_query_log"</span>;</span>  <span class="hljs-comment">-- 已经打开</span><br>+<span class="hljs-comment">----------------+-------+</span><br>| Variable_name  | Value |<br>+<span class="hljs-comment">----------------+-------+</span><br>| slow_query_log | ON    |<br>+<span class="hljs-comment">----------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"long_query_time"</span>;</span><br>+<span class="hljs-comment">-----------------+----------+</span><br>| Variable_name   | Value    |<br>+<span class="hljs-comment">-----------------+----------+</span><br>| long_query_time | 2.000000 |   <span class="hljs-comment">-- my.cnf 中该值设置为2秒</span><br>+<span class="hljs-comment">-----------------+----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"min_ex%"</span>;</span>  <span class="hljs-comment">-- my.cnf 中已经关闭注释，所以这里为0</span><br>+<span class="hljs-comment">------------------------+-------+</span><br>| Variable_name          | Value |<br>+<span class="hljs-comment">------------------------+-------+</span><br>| min_examined_row_limit | 0     |<br>+<span class="hljs-comment">------------------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<ul><li>查看慢查询日志</li>
</ul>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment">#终端B</span><br><span class="hljs-comment">#</span><br>[root@localhost mysql_data]<span class="hljs-comment"># tail -f slow.log </span><br>/usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld, Version: <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log (MySQL Community Server (GPL)). started with:<br>Tcp port: <span class="hljs-number">3306</span>  Unix socket: (null)<br>Time                 Id Command    Argument  <span class="hljs-comment">#测试没有任何慢查询日志信息</span><br></code></pre>

<ul><li>进行模拟耗时操作</li>
</ul>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 终端A</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">sleep</span>(<span class="hljs-number">4</span>);</span><br>+<span class="hljs-comment">----------+</span><br>| sleep(4) |<br>+<span class="hljs-comment">----------+</span><br>|        0 |<br>+<span class="hljs-comment">----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">4.00</span> sec)</span><br></code></pre>

<ul><li>最终产生慢查询日志</li>
</ul>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment">#终端B</span><br><span class="hljs-comment">#</span><br>[root@localhost mysql_data]<span class="hljs-comment"># tail -f slow.log </span><br>/usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld, Version: <span class="hljs-number">5.7</span>.<span class="hljs-number">9</span>-log (MySQL Community Server (GPL)). started with:<br>Tcp port: <span class="hljs-number">3306</span>  Unix socket: (null)<br>Time                 Id Command    Argument  <span class="hljs-comment">#测试没有任何慢查询日志信息</span><br><span class="hljs-comment"># Time: 2015-11-21T07:18:10.741663+08:00</span><br><span class="hljs-comment"># User@Host: root[root] @ localhost []  Id:     2</span><br><span class="hljs-comment"># Query_time: 4.000333  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0 </span><br>                                                          <span class="hljs-comment">#这个就是min_examined_row_limit</span><br>                                                          <span class="hljs-comment">#设置的意义。如my.cnf中设置该值为100</span><br>                                                          <span class="hljs-comment">#则这条语句因为Rows_examined &lt; 100,而不会被记录</span><br>SET timestamp=<span class="hljs-number">1448061490</span>;<br>select sleep(<span class="hljs-number">4</span>);<br></code></pre>

<blockquote>
  <p><strong>注意</strong> <br>
  如果在终端A中<code>set global min_examined_row_limit = 100;</code>, 然后执行<code>select sleep(5);</code>，会发现该记录仍然被记录到慢查询日志中。原因是因为<code>set global min_examined_row_limit</code>设置的是全局变量，此次会话不生效。</p>
  
  <p><strong>但是我们上面<code>set global slow_query_log = 1；</code>却是在线生效的，这点有所不通</strong></p>
</blockquote>

<ul><li>mysqldumpslow</li>
</ul>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[root@localhost mysql_data]<span class="hljs-comment"># mysqldumpslow  slow.log</span><br><br>Reading mysql slow query <span class="hljs-built_in">log</span> from slow.log<br>Count: <span class="hljs-number">2</span>  Time=<span class="hljs-number">0.00</span>s (<span class="hljs-number">0</span>s)  Lock=<span class="hljs-number">0.00</span>s (<span class="hljs-number">0</span>s)  Rows=<span class="hljs-number">0.0</span> (<span class="hljs-number">0</span>), <span class="hljs-number">0</span>users@<span class="hljs-number">0</span>hosts<br>  Time: N-N-<span class="hljs-number">21</span>T07:N:N.N+N:N<br>  <span class="hljs-comment"># User@Host: root[root] @ localhost []  Id:     N</span><br>  <span class="hljs-comment"># Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N</span><br>  SET timestamp=N;<br>  select sleep(N)<br><br>Count: <span class="hljs-number">1</span>  Time=<span class="hljs-number">0.00</span>s (<span class="hljs-number">0</span>s)  Lock=<span class="hljs-number">0.00</span>s (<span class="hljs-number">0</span>s)  Rows=<span class="hljs-number">0.0</span> (<span class="hljs-number">0</span>), <span class="hljs-number">0</span>users@<span class="hljs-number">0</span>hosts<br>  <span class="hljs-comment"># Time: N-N-21T07:N:N.N+N:N</span><br>  <span class="hljs-comment"># User@Host: root[root] @ localhost []  Id:     N</span><br>  <span class="hljs-comment"># Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N</span><br>  SET timestamp=N;<br>  select sleep(N)<br><br><span class="hljs-comment">#######################################################################</span><br><br>[root@localhost mysql_data]<span class="hljs-comment"># mysqldumpslow  --help</span><br>Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]<br><br>Parse and summarize the MySQL slow query log. Options are<br><br>  --verbose    verbose<br>  --debug      debug<br>  --help       write this text to standard output<br><br>  -v           verbose<br>  <span class="hljs-operator">-d</span>           debug<br>  <span class="hljs-operator">-s</span> ORDER     what to sort by (al, at, ar, c, l, r, t), <span class="hljs-string">'at'</span> is default <span class="hljs-comment">#根据以下某个信息来排序</span><br>                al: average lock time<br>                ar: average rows sent<br>                at: average query time<br>                 c: count<br>                 l: lock time<br>                 r: rows sent<br>                 t: query time  <br>  -r           reverse the sort order (largest last instead of first)  <span class="hljs-comment"># 逆序输出</span><br>  -t NUM       just show the top n queries      <span class="hljs-comment"># TOP(n)参数</span><br>  <span class="hljs-operator">-a</span>           don<span class="hljs-string">'t abstract all numbers to N and strings to '</span>S<span class="hljs-string">'<br>  -n NUM       abstract numbers with at least n digits within names<br>  -g PATTERN   grep: only consider stmts that include this string<br>  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),<br>               default is '</span>*<span class="hljs-string">', i.e. match all<br>  -i NAME      name of server instance (if using mysql.server startup script)<br>  -l           don'</span>t subtract lock time from total time<br></code></pre>

<blockquote>
  <p>如果在线上操作，不需要<code>mysqldumpslow</code>去扫整个<code>slow.log</code>， 可以去<code>tail -n 10000 slow.log &gt; last_10000_slow.log</code>(<em>10000这个数字根据实际情况进行调整</em>),然后进行<code>mysqldumpslow last_10000_slow.log</code></p>
</blockquote>

<ul><li>慢查询日志存入表</li>
</ul>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 在my.cnf 中增加 log_output = TABLE，打开slow_query_log选项，然后重启数据库实例</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"log_output%"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| log_output    | TABLE |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"slow_query_log"</span>;</span><br>+<span class="hljs-comment">----------------+-------+</span><br>| Variable_name  | Value |<br>+<span class="hljs-comment">----------------+-------+</span><br>| slow_query_log | ON    |<br>+<span class="hljs-comment">----------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> mysql.slow_log;</span><br>+<span class="hljs-comment">----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+</span><br>| start_time                 | user_host                 | query_time      | lock_time       | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text        | thread_id |<br>+<span class="hljs-comment">----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+</span><br>| 2015-11-20 19:50:28.574677 | root[root] @ localhost [] | 00:00:04.000306 | 00:00:00.000000 |         1 |             0 |    |              0 |         0 |        11 | <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">sleep</span>(<span class="hljs-number">4</span>) |         <span class="hljs-number">3</span> |<br>+<span class="hljs-comment">----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> mysql.slow_log;</span><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 表结构输出省略</span><br><span class="hljs-comment">-- 关键一句如下：</span><br><span class="hljs-comment">--</span><br>ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'  <span class="hljs-comment">-- ENGINE=CSV 这里使用的是CSV的引擎,性能较差</span><br><br><span class="hljs-comment">-- 建议将slow_log表的存储引擎改成MyISAM</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> mysql.slow_log <span class="hljs-keyword">engine</span> = myisam;</span><br>ERROR 1580 (HY000): You cannot '<span class="hljs-operator"><span class="hljs-keyword">ALTER</span><span class="hljs-string">' a log table if logging is enabled  '</span><span class="hljs-comment">-- 提示我正在记录日志中，不能转换</span><br><br>mysql&gt; <span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> slow_query_log = <span class="hljs-number">0</span>;</span>    <span class="hljs-comment">-- 先停止记录日志</span><br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> mysql.slow_log <span class="hljs-keyword">engine</span> = myisam;</span>   <span class="hljs-comment">-- 然后转换表的引擎</span><br>Query OK, 2 rows affected (5.05 sec)<br>Records: 2  Duplicates: 0  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> slow_query_log = <span class="hljs-number">1</span>;</span>     <span class="hljs-comment">-- 再开启记录日志</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> mysql.slow_log;</span><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 表结构输出省略</span><br><span class="hljs-comment">-- 关键一句如下：</span><br><span class="hljs-comment">--</span><br>ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'  <span class="hljs-comment">-- ENGINE 变成了MyISAM</span><br></code></pre>

<blockquote>
  <p>使用<code>TABLE</code>的优势在于方便查询，但是记住当在备份的时候，不要备份慢查询日志的表，避免备份过大。 <br>
  使用<code>FILE</code>也可以，需要定时清除该文件，避免单文件过大。</p>
</blockquote>

<hr>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<h2 id="二-通用日志genericlog与审计">二. 通用日志(generic_log)与审计</h2>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<h3 id="1-通用日志作用">1. 通用日志作用</h3>

<ul><li><p>当需要查找某条特定SQL语句，且该SQL语句执行较快，无法记录到slow_log中时，可以开启通用日志<code>generic_log</code>,进行全面记录， 可用于审计<code>Audit</code></p></li>
<li><p>通用日志会记录所有操作，性能下降明显。所以如果需要审计，需要<code>Audit Plugin</code></p></li>
</ul>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<h3 id="2-审计插件">2. 审计插件</h3>

<ul><li><p>MariaDB Audit 插件</p>

<ul>
<li>MySQL社区版本目前没有提供Audit的功能，企业版本提供了该功能。MariaDB 提供了开源的Audit插件，且MySQL也能使用。</li></ul></li>
<li><p>插件下载</p>

<ul>
<li><a href="https://downloads.mariadb.com/enterprise/6ahb-eete/mariadb-audit-plugin/server_audit-1.2.0.tar.gz" target="_blank">server_audit-1.2.0.tar.gz</a> 上述链接如果失效，可以进入官方页面注册，然后下载</li>
<li><a href="https://mariadb.com/my_portal/download/audit_plugin" target="_blank">官方注册下载插件</a></li></ul></li>
</ul>

</div><div id="wmd-preview-section-815" class="wmd-preview-section preview-content">

<h3 id="3-audit-plugin安装">3.  Audit Plugin安装</h3>

<ul><li><p><strong><code>MySQL5.7.9</code> 审计插件安装失败，提示如下：</strong></p>

<blockquote>
  <p><code>ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/server_audit.so' (errno: 13 /usr/lib64/mysql/plugin/server_audit.so: undefined symbol: _my_thread_var)</code></p>
</blockquote></li>
<li><p><strong>MySQL5.6.27 审计插件安装成功，步骤如下：</strong></p></li>
</ul></div><div id="wmd-preview-section-2716" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment"># 找到plugin位置</span><br>[root@localhost ~]&gt; cat /etc/my.cnf  | grep plugin_dir<br>plugin_dir=/usr/<span class="hljs-built_in">local</span>/mysql/lib/plugin<br><br><span class="hljs-comment"># 解压plugin</span><br>[root@localhost ~]&gt; tar zxvf server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>.tar.gz<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">32</span>_debug/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">32</span>_debug/server_audit.so<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">32</span>/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">32</span>/server_audit.so<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">64</span>_debug/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">64</span>_debug/server_audit.so<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">32</span>/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">32</span>/server_audit.dll<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">64</span>_debug/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">64</span>_debug/server_audit.dll<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">64</span>/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">64</span>/server_audit.so<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">64</span>/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">64</span>/server_audit.dll<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">32</span>_debug/<br>server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/windows-<span class="hljs-number">32</span>_debug/server_audit.dll<br><br><span class="hljs-comment"># 移动插件到对应的插件目录</span><br>[root@localhost ~]&gt; mv server_audit-<span class="hljs-number">1.2</span>.<span class="hljs-number">0</span>/linux-<span class="hljs-number">64</span>/server_audit.so /usr/<span class="hljs-built_in">local</span>/mysql/lib/plugin<br>[root@localhost ~]&gt; <span class="hljs-built_in">cd</span> /usr/<span class="hljs-built_in">local</span>/mysql/lib/plugin<br></code></pre></div><div id="wmd-preview-section-2033" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 相关安装步骤</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">version</span>();</span><br>+<span class="hljs-comment">------------+</span><br>| version()  |<br>+<span class="hljs-comment">------------+</span><br>| 5.6.27-log |<br>+<span class="hljs-comment">------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">INSTALL</span> <span class="hljs-keyword">PLUGIN</span> server_audit <span class="hljs-keyword">SONAME</span> <span class="hljs-string">'server_audit.so'</span>;</span>  <span class="hljs-comment">-- 安装插件，该步骤在5.7.9中失败</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"%server_audit%"</span>;</span>    <span class="hljs-comment">-- 查看和server_audit相关的参数</span><br>+<span class="hljs-comment">-------------------------------+-----------------------+</span><br>| Variable_name                 | Value                 |<br>+<span class="hljs-comment">-------------------------------+-----------------------+</span><br>| server_audit_events           |                       |<br>| server_audit_excl_users       |                       |<br>| server_audit_file_path        | server_audit.log      |<br>| server_audit_file_rotate_now  | OFF                   |<br>| server_audit_file_rotate_size | 1000000               |<br>| server_audit_file_rotations   | 9                     |<br>| server_audit_incl_users       |                       |<br>| server_audit_logging          | OFF                   |<br>| server_audit_mode             | 1                     |<br>| server_audit_output_type      | file                  |<br>| server_audit_syslog_facility  | LOG_USER              |<br>| server_audit_syslog_ident     | mysql-server_auditing |<br>| server_audit_syslog_info      |                       |<br>| server_audit_syslog_priority  | LOG_INFO              |<br>+<span class="hljs-comment">-------------------------------+-----------------------+</span><br>14 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> server_audit_logging = <span class="hljs-number">1</span>;</span>   <span class="hljs-comment">-- 打开审计功能</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"server_audit_logging"</span>;</span> <br>+<span class="hljs-comment">----------------------+-------+</span><br>| Variable_name        | Value |<br>+<span class="hljs-comment">----------------------+-------+</span><br>| server_audit_logging | ON    |<br>+<span class="hljs-comment">----------------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">status</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'%audit%'</span>;</span><br>+<span class="hljs-comment">----------------------------+------------------+</span><br>| Variable_name              | Value            |<br>+<span class="hljs-comment">----------------------------+------------------+</span><br>| server_audit_active        | ON               |<br>| server_audit_current_log   | server_audit.log |<br>| server_audit_last_error    |                  |<br>| server_audit_writes_failed | 0                |<br>+<span class="hljs-comment">----------------------------+------------------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre></div><div id="wmd-preview-section-2844" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment">#查看审计日志</span><br><span class="hljs-comment">#</span><br>[root@MyServer mysql_data]&gt; tail <span class="hljs-operator">-f</span> server_audit.log <br><span class="hljs-number">20151120</span> <span class="hljs-number">22</span>:<span class="hljs-number">40</span>:<span class="hljs-number">54</span>,MyServer,root,localhost,<span class="hljs-number">2</span>,<span class="hljs-number">9</span>,QUERY,,<span class="hljs-string">'set global server_audit_logging = 1'</span>,<span class="hljs-number">0</span><br><span class="hljs-number">20151120</span> <span class="hljs-number">22</span>:<span class="hljs-number">41</span>:<span class="hljs-number">16</span>,MyServer,root,localhost,<span class="hljs-number">2</span>,<span class="hljs-number">10</span>,QUERY,,<span class="hljs-string">'show variables like "server_audit_logging"'</span>,<span class="hljs-number">0</span><br><span class="hljs-number">20151120</span> <span class="hljs-number">22</span>:<span class="hljs-number">41</span>:<span class="hljs-number">53</span>,MyServer,root,localhost,<span class="hljs-number">1</span>,<span class="hljs-number">5</span>,QUERY,,<span class="hljs-string">'show status like \'</span>%audit%\<span class="hljs-string">''</span>,<span class="hljs-number">0</span><br></code></pre>

<blockquote>
  <p>以上仅为基本功能操作，详细的细粒度控制请参考<a href="https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/" target="_blank">官方文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-4770" class="wmd-preview-section preview-content">

<h2 id="三-存储引擎一">三. 存储引擎(一)</h2></div><div id="wmd-preview-section-4610" class="wmd-preview-section preview-content">

<h3 id="1mysql上支持的存储引擎">1.Mysql上支持的存储引擎</h3>

</div><div id="wmd-preview-section-4611" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">engines</span>;</span><br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |<br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |<br>| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |<br>| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |<br>| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |<br>| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |<br>| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |<br>| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |<br>| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |<br>| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |<br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-4612" class="wmd-preview-section preview-content">

<h3 id="2-存储引擎的概念">2. 存储引擎的概念</h3>

<blockquote>
  <p>用来处理数据库的相关CRUD操作</p>
</blockquote>

<p>每个数据库都有存储引擎，只是MySQL比较强调存储引擎的概念。</p>

</div><div id="wmd-preview-section-7672" class="wmd-preview-section preview-content">

<h3 id="3-mysql存储引擎">3. MySQL存储引擎</h3>

<p></p><ul><li>官方存储引擎 <br>
<ul>
<li>MyISAM</li>
<li><code>InnoDB</code>  – 推荐；其他引擎已经体停止维护和开发</li>
<li>Memory</li>
<li>Federated</li>
<li>CSV</li>
<li>Archive</li></ul></li>
<li>第三方存储引擎 <br>
<ul>
<li>TokuDB  – 开源，适合插入密集型</li>
<li>InfoBright  – 商业，开源版本有数据量限制。属于列存储，面向OLAP场景</li>
<li>Spider <br></li></ul></li></ul><p></p>

<blockquote>
  <p>第三方存储引擎在特定场合下比较适合，除此之外，都应该使用InnoDB
  </p>
</blockquote></div><div id="wmd-preview-section-10361" class="wmd-preview-section preview-content">

<h3 id="3-存储引擎之myisam">3. 存储引擎之MyISAM</h3>

<ul><li>MySQL5.1版本之前的默认存储引擎</li>
<li>堆表数据结构</li>
<li>表锁设计</li>
<li>支持数据静态压缩</li>
<li>不支持事物</li>
<li>数据容易丢失</li>
<li>索引容易损坏</li>
<li>唯一优点 <br>
<ul>
<li>数据文件可以直接拷贝到另一台服务器使用</li></ul></li>
</ul>

<blockquote>
  <p>现在MySQL中还有用MyISAM的表，主要是历史原因。数据库文件以<code>MY</code>开头的基本都是MyISAM的表</p>
</blockquote></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>